"""
数据库服务器 - 多线程数据库系统的主入口
"""

import os
import sys
import time
import threading
import os
from typing import Dict, List, Optional, Any, Tuple
from dataclasses import dataclass
from enum import Enum
import uuid

# 添加当前目录到Python路径
current_dir = os.path.dirname(os.path.abspath(__file__))
if current_dir not in sys.path:
    sys.path.insert(0, current_dir)

try:
    from storage_engine import StorageEngine, CachePolicy
    from thread_manager import DatabaseThreadPool, TaskType, TaskPriority
    from page_lock_manager import ThreadSafeLockManager
    from table_schema import Column, ColumnType
    from statistics import Logger
    from database_manager import DatabaseManager
    from database_instance import DatabaseInstance
    from system_database_manager import SystemDatabaseManager
    
    # 导入UserManagement组件
    parent_dir = os.path.dirname(current_dir)
    user_management_dir = os.path.join(parent_dir, "UserManagement")
    if user_management_dir not in sys.path:
        sys.path.insert(0, user_management_dir)
    
    try:
        from auth_manager import AuthManager
        from privilege_types import PermissionResult
    except ImportError:
        # 如果相对导入失败，尝试绝对导入
        sys.path.append(user_management_dir)
        from auth_manager import AuthManager
        from privilege_types import PermissionResult
except ImportError as e:
    print(f"Import error: {e}")
    sys.exit(1)


@dataclass
class DatabaseConfig:
    """数据库配置"""
    db_path: str = "test_db"
    cache_size: int = 100
    cache_policy: CachePolicy = CachePolicy.LRU
    enable_locking: bool = True
    max_query_workers: int = 4
    max_io_workers: int = 2
    max_maintenance_workers: int = 1
    connection_timeout: int = 30
    query_timeout: int = 10


@dataclass
class ConnectionSession:
    """连接会话"""
    session_id: str
    transaction_id: Optional[str]
    created_time: float
    last_active_time: float
    current_database: Optional[str] = None  # 当前使用的数据库
    is_active: bool = True
    
    # 新增用户认证相关字段
    username: Optional[str] = None
    user_host: Optional[str] = None
    is_authenticated: bool = False
    auth_session_id: Optional[str] = None  # AuthManager中的会话ID
    
    def update_activity(self):
        """更新活动时间"""
        self.last_active_time = time.time()


class DatabaseServer:
    """
    多线程数据库服务器
    
    整合存储引擎、线程池和锁管理器，提供：
    1. 多线程查询处理
    2. 连接会话管理  
    3. 事务管理
    4. 并发控制
    """
    
    def __init__(self, config: DatabaseConfig):
        self.config = config
        self.logger = Logger()
        
        # 初始化多数据库管理器
        self.database_manager = DatabaseManager(
            data_root=config.db_path,
            default_cache_size=config.cache_size,
            default_cache_policy=config.cache_policy,
            enable_locking=config.enable_locking
        )
        
        # 初始化系统数据库管理器
        self.system_db_manager = SystemDatabaseManager(self.database_manager)
        
        # 初始化用户认证管理器
        self.auth_manager = None  # 延迟初始化，避免循环依赖
        
        # 初始化线程池
        self.thread_pool = DatabaseThreadPool(
            query_workers=config.max_query_workers,
            io_workers=config.max_io_workers,
            maintenance_workers=config.max_maintenance_workers
        )
        
        # 初始化锁管理器
        self.lock_manager = ThreadSafeLockManager()
        
        # 连接会话管理
        self.sessions: Dict[str, ConnectionSession] = {}
        self.sessions_lock = threading.RLock()
        
        # 服务器状态
        self.is_running = False
        self.start_time = time.time()
        
        self.logger.info("DB_SERVER", 0, f"数据库服务器初始化完成，配置: {config}")
    
    def start(self):
        """启动数据库服务器"""
        if self.is_running:
            raise RuntimeError("数据库服务器已经在运行")
        
        # 1. 确保系统数据库存在
        success = self.system_db_manager.ensure_system_database()
        if not success:
            raise RuntimeError("Failed to initialize system database")
        
        # 1.5. 验证系统数据库已正确加载到database_manager中（但不阻止启动）
        self.logger.info("DB_SERVER", 0, f"Available databases in manager: {list(self.database_manager.databases.keys())}")
        system_db = self.database_manager.get_database("system")
        if not system_db:
            self.logger.warn("DB_SERVER", 0, "System database not immediately available in database_manager, will be loaded on demand")
            # 尝试检查系统数据库目录是否存在
            system_db_path = os.path.join(self.database_manager.data_root, "system")
            self.logger.info("DB_SERVER", 0, f"System database directory exists: {os.path.exists(system_db_path)}")
            if os.path.exists(system_db_path):
                self.logger.info("DB_SERVER", 0, f"System database directory contents: {os.listdir(system_db_path)}")
        else:
            self.logger.info("DB_SERVER", 0, f"System database verified in database_manager with {len(system_db.list_tables())} tables")
        
        # 2. 初始化认证管理器
        self._initialize_auth_manager()
        
        # 3. 创建默认root用户（如果不存在）
        self._ensure_default_root_user()
        
        self.is_running = True
        self.start_time = time.time()
        # 线程池在初始化时已经启动，无需再次启动
        self.logger.info("DB_SERVER", 0, "数据库服务器启动完成")
    
    def stop(self):
        """停止数据库服务器"""
        if not self.is_running:
            return
        
        self.is_running = False
        
        # 关闭所有会话
        with self.sessions_lock:
            for session in self.sessions.values():
                session.is_active = False
        
        # 停止线程池
        self.thread_pool.shutdown()
        
        # 关闭数据库管理器
        self.database_manager.close_all()
        
        self.logger.info("DB_SERVER", 0, "数据库服务器停止完成")
    
    def _initialize_auth_manager(self):
        """初始化认证管理器"""
        try:
            self.auth_manager = AuthManager(self.database_manager)
            self.logger.info("AUTH", 0, "认证管理器初始化完成")
        except Exception as e:
            self.logger.error("AUTH", 0, f"认证管理器初始化失败: {e}")
            raise
    
    def _ensure_default_root_user(self):
        """确保默认root用户存在"""
        try:
            # 创建默认root用户
            self.auth_manager.user_manager.create_default_root_user()
            
            # 为root用户授予所有权限
            self.auth_manager.permission_manager.create_default_root_privileges()
            
            self.logger.info("AUTH", 0, "默认root用户初始化完成")
        except Exception as e:
            self.logger.warn("AUTH", 0, f"默认root用户初始化失败: {e}")
    
    def create_session(self) -> str:
        """创建新的连接会话"""
        session_id = str(uuid.uuid4())
        session = ConnectionSession(
            session_id=session_id,
            transaction_id=None,
            created_time=time.time(),
            last_active_time=time.time()
        )
        
        with self.sessions_lock:
            self.sessions[session_id] = session
        
        self.logger.info("SESSION", 0, f"创建新会话: {session_id}")
        return session_id
    
    def authenticate_and_create_session(self, username: str, password: str, 
                                       client_host: str = None) -> Optional[str]:
        """
        用户认证并创建会话
        
        Args:
            username: 用户名
            password: 密码
            client_host: 客户端主机
            
        Returns:
            Optional[str]: 认证成功返回session_id，否则返回None
        """
        try:
            if not self.auth_manager:
                self.logger.error("AUTH", 0, "Authentication manager not initialized")
                return None
            
            # 通过AuthManager进行认证
            auth_session_id = self.auth_manager.authenticate(username, password, client_host)
            
            if auth_session_id:
                # 认证成功，创建DatabaseServer会话
                db_session_id = str(uuid.uuid4())
                
                # 获取认证会话信息
                auth_session = self.auth_manager.validate_session(auth_session_id)
                if not auth_session:
                    return None
                
                session = ConnectionSession(
                    session_id=db_session_id,
                    transaction_id=None,
                    created_time=time.time(),
                    last_active_time=time.time(),
                    username=username,
                    user_host=auth_session.user_info.host,
                    is_authenticated=True,
                    auth_session_id=auth_session_id
                )
                
                with self.sessions_lock:
                    self.sessions[db_session_id] = session
                
                self.logger.info("AUTH", 0, f"用户 {username}@{auth_session.user_info.host} 认证成功，会话: {db_session_id}")
                return db_session_id
            else:
                self.logger.warn("AUTH", 0, f"用户 {username} 认证失败")
                return None
                
        except Exception as e:
            self.logger.error("AUTH", 0, f"认证过程出错: {e}")
            return None
    
    def close_session(self, session_id: str):
        """关闭连接会话"""
        with self.sessions_lock:
            if session_id in self.sessions:
                session = self.sessions[session_id]
                
                # 如果有活跃事务，回滚
                if session.transaction_id:
                    self.rollback_transaction(session_id)
                
                # 如果是认证会话，同时关闭AuthManager中的会话
                if session.is_authenticated and session.auth_session_id and self.auth_manager:
                    try:
                        self.auth_manager.logout(session.auth_session_id)
                    except Exception as e:
                        self.logger.warn("AUTH", 0, f"关闭认证会话失败: {e}")
                
                session.is_active = False
                del self.sessions[session_id]
                self.logger.info("SESSION", 0, f"关闭会话: {session_id}")
    
    def get_session(self, session_id: str) -> Optional[ConnectionSession]:
        """获取会话信息"""
        with self.sessions_lock:
            session = self.sessions.get(session_id)
            if session and session.is_active:
                session.update_activity()
                return session
            return None
    
    def begin_transaction(self, session_id: str) -> str:
        """开始事务"""
        session = self.get_session(session_id)
        if not session:
            raise ValueError(f"会话不存在: {session_id}")
        
        if session.transaction_id:
            raise RuntimeError(f"会话 {session_id} 已有活跃事务")
        
        transaction_id = str(uuid.uuid4())
        session.transaction_id = transaction_id
        
        # 在锁管理器中开始事务
        self.lock_manager.begin_transaction(transaction_id)
        
        # 设置当前事务ID到数据库实例
        if session.current_database:
            try:
                db_instance = self.database_manager.get_database(session.current_database)
                if db_instance:
                    # 设置当前事务ID
                    db_instance.set_current_transaction(transaction_id)
                    
                    # WAL记录：写入事务开始记录
                    if hasattr(db_instance, '_ensure_wal_initialized'):
                        if db_instance._ensure_wal_initialized():
                            db_instance.wal_manager.write_transaction_begin(transaction_id)
            except Exception as e:
                self.logger.warn("WAL_BEGIN", 0, f"Failed to write WAL begin record: {e}")
        
        self.logger.info("TRANSACTION", 0, f"会话 {session_id} 开始事务 {transaction_id}")
        return transaction_id
    
    def commit_transaction(self, session_id: str):
        """提交事务"""
        session = self.get_session(session_id)
        if not session:
            raise ValueError(f"会话不存在: {session_id}")
        
        if not session.transaction_id:
            raise RuntimeError(f"会话 {session_id} 没有活跃事务")
        
        transaction_id = session.transaction_id
        
        # WAL记录：写入事务提交记录并强制刷新
        if session.current_database:
            try:
                db_instance = self.database_manager.get_database(session.current_database)
                if db_instance:
                    # WAL记录
                    if hasattr(db_instance, '_ensure_wal_initialized'):
                        if db_instance._ensure_wal_initialized():
                            db_instance.wal_manager.write_transaction_commit(transaction_id)
                            db_instance.wal_manager.force_flush()  # 强制刷新WAL到磁盘
                    
                    # 清除当前事务ID
                    db_instance.clear_current_transaction()
            except Exception as e:
                self.logger.error("WAL_COMMIT", 0, f"Failed to write WAL commit record: {e}")
                # WAL失败应该导致事务失败 - 但在初始化阶段可能还没有WAL
                # 所以降级为警告而不是错误
                self.logger.warn("WAL_COMMIT", 0, f"WAL commit warning: {e}")
        
        # 在锁管理器中提交事务
        self.lock_manager.commit_transaction(transaction_id)
        
        session.transaction_id = None
        self.logger.info("TRANSACTION", 0, f"会话 {session_id} 提交事务 {transaction_id}")
    
    def rollback_transaction(self, session_id: str):
        """回滚事务"""
        session = self.get_session(session_id)
        if not session:
            raise ValueError(f"会话不存在: {session_id}")
        
        if not session.transaction_id:
            return  # 没有活跃事务，直接返回
        
        transaction_id = session.transaction_id
        
        # WAL记录：写入事务回滚记录
        if session.current_database:
            try:
                db_instance = self.database_manager.get_database(session.current_database)
                if db_instance:
                    # WAL记录
                    if hasattr(db_instance, '_ensure_wal_initialized'):
                        if db_instance._ensure_wal_initialized():
                            db_instance.wal_manager.write_transaction_rollback(transaction_id)
                            db_instance.wal_manager.force_flush()
                    
                    # 清除当前事务ID
                    db_instance.clear_current_transaction()
            except Exception as e:
                self.logger.warn("WAL_ROLLBACK", 0, f"Failed to write WAL rollback record: {e}")
        
        # 在锁管理器中回滚事务
        self.lock_manager.rollback_transaction(transaction_id)
        
        session.transaction_id = None
        self.logger.info("TRANSACTION", 0, f"会话 {session_id} 回滚事务 {transaction_id}")
    
    def execute_query(self, session_id: str, operation: str, **kwargs) -> Any:
        """执行查询操作（向后兼容接口，建议使用 execute_database_operation）"""
        self.logger.warn("DEPRECATED", 0, "execute_query is deprecated, use execute_database_operation instead")
        
        # 路由到新的数据库操作接口
        session = self.get_session(session_id)
        if not session or not session.current_database:
            raise ValueError(f"会话 {session_id} 未选择数据库，请先使用 use_database")
        
        return self.execute_database_operation(session_id, operation, **kwargs)
    
    # 已移除_get_task_type方法，因为不再使用线程池异步执行
    
    # _execute_operation 已移除，统一使用 execute_database_operation
    
    # 旧的单数据库兼容方法已移除，统一使用 execute_database_operation
    
    # ==================== 多数据库操作接口 ====================
    
    def create_database(self, session_id: str, database_name: str, charset: str = "utf8") -> bool:
        """
        创建数据库
        
        Args:
            session_id: 会话ID
            database_name: 数据库名称
            charset: 字符集
            
        Returns:
            bool: 创建是否成功
        """
        session = self.get_session(session_id)
        if not session:
            self.logger.error("CREATE_DATABASE", 0, f"Invalid session: {session_id}")
            return False
        
        try:
            # 权限检查：需要 CREATE 数据库权限（映射在 UserManagement.OPERATION_PRIVILEGE_MAP）
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "create_database", database_name, None)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to create database '{database_name}': {perm.reason}")
                    return False
            success = self.database_manager.create_database(database_name, charset)
            if success:
                self.logger.info("CREATE_DATABASE", 0, f"Database '{database_name}' created by session {session_id}")
            return success
        except Exception as e:
            self.logger.error("CREATE_DATABASE", 0, f"Failed to create database '{database_name}': {e}")
            return False
    
    def drop_database(self, session_id: str, database_name: str, force: bool = False) -> bool:
        """
        删除数据库
        
        Args:
            session_id: 会话ID
            database_name: 数据库名称
            force: 是否强制删除
            
        Returns:
            bool: 删除是否成功
        """
        session = self.get_session(session_id)
        if not session:
            self.logger.error("DROP_DATABASE", 0, f"Invalid session: {session_id}")
            return False
        
        try:
            # 权限检查：需要 DROP 数据库权限
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "drop_database", database_name, None)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to drop database '{database_name}': {perm.reason}")
                    return False
            success = self.database_manager.drop_database(database_name, force)
            if success:
                self.logger.info("DROP_DATABASE", 0, f"Database '{database_name}' dropped by session {session_id}")
                
                # 如果会话当前使用的是被删除的数据库，清除数据库上下文
                if session.current_database == database_name:
                    session.current_database = None
            
            return success
        except Exception as e:
            self.logger.error("DROP_DATABASE", 0, f"Failed to drop database '{database_name}': {e}")
            return False
    
    def use_database(self, session_id: str, database_name: str) -> bool:
        """
        切换当前数据库
        
        Args:
            session_id: 会话ID
            database_name: 数据库名称
            
        Returns:
            bool: 切换是否成功
        """
        session = self.get_session(session_id)
        if not session:
            self.logger.error("USE_DATABASE", 0, f"Invalid session: {session_id}")
            return False
        
        # 检查用户是否已认证
        if not session.is_authenticated:
            self.logger.error("AUTH", 0, f"Session {session_id} not authenticated")
            return False
        
        try:
            # 检查数据库是否存在
            if not self.database_manager.database_exists(database_name):
                self.logger.error("USE_DATABASE", 0, f"Database '{database_name}' does not exist")
                return False
            
            # 权限检查：检查用户是否有访问该数据库的权限
            if self.auth_manager:
                try:
                    perm_result = self.auth_manager.check_permission(
                        session.auth_session_id, 
                        "use_database", 
                        database_name, 
                        None
                    )
                    
                    if not perm_result.allowed:
                        self.logger.warn("AUTH", 0, f"User {session.username} denied access to database {database_name}: {perm_result.reason}")
                        return False
                except Exception as e:
                    self.logger.error("AUTH", 0, f"Permission check failed for database {database_name}: {e}")
                    # 对于root用户，允许访问
                    if session.username == "root":
                        self.logger.info("AUTH", 0, f"Allowing root user access to database {database_name}")
                    else:
                        return False
            
            # 切换数据库上下文
            session.current_database = database_name
            session.update_activity()
            
            self.logger.info("USE_DATABASE", 0, f"Session {session_id} switched to database '{database_name}'")
            return True
            
        except Exception as e:
            self.logger.error("USE_DATABASE", 0, f"Failed to use database '{database_name}': {e}")
            return False
    
    def list_databases(self, session_id: str) -> List[str]:
        """
        获取数据库列表
        
        Args:
            session_id: 会话ID
            
        Returns:
            List[str]: 数据库名称列表
        """
        session = self.get_session(session_id)
        if not session:
            self.logger.error("LIST_DATABASES", 0, f"Invalid session: {session_id}")
            return []
        
        try:
            # 权限检查：列出数据库需要USAGE权限（全局）
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "list_databases", None, None)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to list databases: {perm.reason}")
                    return []
            databases = self.database_manager.list_databases()
            self.logger.info("LIST_DATABASES", 0, f"Listed {len(databases)} databases for session {session_id}")
            return databases
        except Exception as e:
            self.logger.error("LIST_DATABASES", 0, f"Failed to list databases: {e}")
            return []
    
    def get_current_database(self, session_id: str) -> Optional[str]:
        """获取会话当前使用的数据库"""
        session = self.get_session(session_id)
        if not session:
            return None
        return session.current_database
    
    def execute_database_operation(self, session_id: str, operation: str, **kwargs) -> Any:
        """
        在当前数据库上执行操作（支持线程池并发）
        
        Args:
            session_id: 会话ID
            operation: 操作类型
            **kwargs: 操作参数
            
        Returns:
        
            Any: 操作结果
        """
        session = self.get_session(session_id)
        if not session:
            self.logger.error("DB_OPERATION", 0, f"Invalid session: {session_id}")
            return None
        
        # 检查用户是否已认证
        if not session.is_authenticated:
            self.logger.error("AUTH", 0, f"Session {session_id} not authenticated")
            return None
        
        # 权限检查
        if self.auth_manager:
            table_name = kwargs.get('table_name')
            perm_result = self.auth_manager.check_permission(
                session.auth_session_id, 
                operation, 
                session.current_database, 
                table_name
            )
            
            if not perm_result.allowed:
                self.logger.warn("AUTH", 0, f"Permission denied for {session.username}: {perm_result.reason}")
                # 统一返回各操作的“失败默认值”，避免上层把字典當作真值誤判為成功
                default_failures = {
                    "select_records": [],
                    "insert_record": False,
                    "update_records": 0,
                    "delete_records": 0,
                    "create_table": False,
                    "drop_table": False,
                    "list_tables": [],
                    "get_table_schema": None,
                }
                return default_failures.get(operation, None)
        
        if not session.current_database:
            self.logger.error("DB_OPERATION", 0, f"No database selected for session {session_id}")
            return None
        
        # 直接同步执行数据库操作（简化架构）
        try:
            result = self._execute_database_operation_internal(
                session_id=session_id,
                operation=operation,
                **kwargs
            )
            return result
        except Exception as e:
            self.logger.error("DB_OPERATION", 0, f"Operation failed: session={session_id}, operation={operation}, error={e}")
            raise
    
    def _execute_database_operation_internal(self, session_id: str, operation: str, **kwargs) -> Any:
        """
        内部数据库操作执行逻辑（同步执行）
        
        Args:
            session_id: 会话ID
            operation: 操作类型
            **kwargs: 操作参数
            
        Returns:
            Any: 操作结果
        """
        session = self.get_session(session_id)
        if not session:
            raise ValueError(f"Session does not exist: {session_id}")
        
        if not session.current_database:
            raise ValueError(f"No database selected for session {session_id}")
        
        try:
            # 获取数据库实例
            db_instance = self.database_manager.get_database(session.current_database)
            if not db_instance:
                raise ValueError(f"Database '{session.current_database}' not found")
            
            # 执行操作
            if operation == "create_table":
                return db_instance.create_table(kwargs.get('table_name'), kwargs.get('columns', []))
            elif operation == "drop_table":
                return db_instance.drop_table(kwargs.get('table_name'))
            elif operation == "insert_record":
                return db_instance.insert_record(
                    kwargs.get('table_name'), 
                    kwargs.get('record_data', {})
                )
            elif operation == "select_records":
                return db_instance.select_records(kwargs.get('table_name'), kwargs.get('condition'))
            elif operation == "update_records":
                return db_instance.update_records(
                    kwargs.get('table_name'), 
                    kwargs.get('condition'), 
                    kwargs.get('updates')
                )
            elif operation == "delete_records":
                return db_instance.delete_records(
                    kwargs.get('table_name'), 
                    kwargs.get('condition')
                )
            elif operation == "list_tables":
                tables = db_instance.list_tables()
                return tables if tables is not None else []
            elif operation == "get_table_schema":
                schema = db_instance.get_table_schema(kwargs.get('table_name'))
                if schema:
                    # 转换为字典格式以便 ExecutionEngine 使用
                    return {
                        'table_name': schema.table_name,
                        'columns': [
                            {
                                'name': col.name,
                                'type': col.type.value,
                                'length': col.length,
                                'nullable': col.nullable,
                                'primary_key': col.primary_key
                            }
                            for col in schema.columns
                        ],
                        'created_time': schema.created_time.isoformat(),
                        'page_count': len(schema.page_ids) if hasattr(schema, 'page_ids') else 0
                    }
                return None
            else:
                raise ValueError(f"Unknown operation: {operation}")
                
        except Exception as e:
            self.logger.error("DB_OPERATION_INTERNAL", 0, f"Failed to execute operation '{operation}': {e}")
            raise
    
    def get_database_engine(self, session_id: str, database_name: str = None):
        """
        获取数据库引擎实例
        
        Args:
            session_id: 会话ID
            database_name: 数据库名称，如果为None则使用当前会话的数据库
            
        Returns:
            数据库引擎实例或None
        """
        session = self.get_session(session_id)
        if not session:
            return None
        
        # 确定要获取的数据库名称
        target_database = database_name or session.current_database
        if not target_database:
            return None
        
        # 通过数据库管理器获取数据库实例
        db_instance = self.database_manager.get_database(target_database)
        return db_instance
    
    def get_server_stats(self) -> Dict[str, Any]:
        """获取服务器统计信息"""
        with self.sessions_lock:
            active_sessions = len([s for s in self.sessions.values() if s.is_active])
            active_transactions = len([s for s in self.sessions.values() if s.transaction_id])
        
        # 获取系统级统计
        system_stats = self.database_manager.get_system_stats()
        thread_stats = self.thread_pool.get_stats()
        
        return {
            'server': {
                'is_running': self.is_running,
                'uptime_seconds': time.time() - self.start_time,
                'active_sessions': active_sessions,
                'active_transactions': active_transactions,
                'total_sessions': len(self.sessions)
            },
            'database_system': system_stats,
            'threads': thread_stats,
            'config': {
                'data_root': self.config.db_path,
                'default_cache_size': self.config.cache_size,
                'default_cache_policy': self.config.cache_policy.value,
                'enable_locking': self.config.enable_locking
            }
        }
    
    def cleanup_inactive_sessions(self, timeout_seconds: int = None):
        """清理非活跃会话"""
        if timeout_seconds is None:
            timeout_seconds = self.config.connection_timeout
        
        current_time = time.time()
        to_remove = []
        
        with self.sessions_lock:
            for session_id, session in self.sessions.items():
                if (current_time - session.last_active_time) > timeout_seconds:
                    to_remove.append(session_id)
        
        for session_id in to_remove:
            self.close_session(session_id)
        
        if to_remove:
            self.logger.info("CLEANUP", 0, f"清理了 {len(to_remove)} 个非活跃会话")
    
    # ==================== 用户管理接口 ====================
    
    def create_user(self, session_id: str, username: str, password: str, host: str = '%') -> bool:
        """
        创建新用户
        
        Args:
            session_id: 会话ID
            username: 用户名
            password: 密码
            host: 主机（默认为%表示任意主机）
            
        Returns:
            bool: 创建成功返回True
        """
        if not self.auth_manager:
            self.logger.error("USER_MGMT", 0, "Auth manager not initialized")
            return False
        
        try:
            session = self.get_session(session_id)
            if not session or not session.is_authenticated:
                self.logger.error("USER_MGMT", 0, "User not authenticated")
                return False

            # 权限检查：需要 CREATE_USER 权限
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "create_user", None, None)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to create user '{username}@{host}': {perm.reason}")
                    return False

            # 使用 AuthManager 的受控入口创建用户（其内部会做审计等）
            success = self.auth_manager.user_manager.create_user(username, password, host)
            if success:
                self.logger.info("USER_MGMT", 0, f"Created user: {username}@{host}")
            return success
            
        except Exception as e:
            self.logger.error("USER_MGMT", 0, f"Failed to create user {username}: {e}")
            return False
    
    def drop_user(self, session_id: str, username: str, host: str = '%') -> bool:
        """
        删除用户
        
        Args:
            session_id: 会话ID
            username: 用户名
            host: 主机
            
        Returns:
            bool: 删除成功返回True
        """
        if not self.auth_manager:
            self.logger.error("USER_MGMT", 0, "Auth manager not initialized")
            return False
        
        try:
            session = self.get_session(session_id)
            if not session or not session.is_authenticated:
                self.logger.error("USER_MGMT", 0, "User not authenticated")
                return False

            # 权限检查：需要 DROP_USER 权限
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "drop_user", None, None)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to drop user '{username}@{host}': {perm.reason}")
                    return False

            # 使用用户管理器删除用户
            success = self.auth_manager.user_manager.drop_user(username, host)
            if success:
                self.logger.info("USER_MGMT", 0, f"Dropped user: {username}@{host}")
            return success
            
        except Exception as e:
            self.logger.error("USER_MGMT", 0, f"Failed to drop user {username}: {e}")
            return False
    
    def list_users(self, session_id: str) -> List[Dict[str, Any]]:
        """
        列出所有用户
        
        Args:
            session_id: 会话ID
            
        Returns:
            List[Dict[str, Any]]: 用户列表
        """
        if not self.auth_manager:
            self.logger.error("USER_MGMT", 0, "Auth manager not initialized")
            return []
        
        try:
            session = self.get_session(session_id)
            if not session or not session.is_authenticated:
                self.logger.error("USER_MGMT", 0, "User not authenticated")
                return []
            # 权限检查：需要SUPER（或根据你的策略）
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "list_users", None, None)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to list users: {perm.reason}")
                    return []
            
            # 使用用户管理器获取用户列表
            users = self.auth_manager.user_manager.list_users()
            if isinstance(users, list):
                # 将 UserInfo 对象转换为字典格式
                user_dicts = []
                for user in users:
                    if hasattr(user, '__dict__'):
                        user_dict = {
                            'username': user.username,
                            'host': user.host,
                            'created_time': user.created_time.isoformat() if hasattr(user.created_time, 'isoformat') else str(user.created_time),
                            'last_login': user.last_login.isoformat() if hasattr(user.last_login, 'isoformat') else str(user.last_login),
                            'is_active': user.is_active,
                            'max_connections': user.max_connections
                        }
                        user_dicts.append(user_dict)
                return user_dicts
            else:
                return []
            
        except Exception as e:
            self.logger.error("USER_MGMT", 0, f"Failed to list users: {e}")
            return []
    
    def grant_privilege(self, session_id: str, username: str, host: str, 
                       privilege: str, database: str = None, table: str = None) -> bool:
        """
        授予权限
        
        Args:
            session_id: 会话ID
            username: 用户名
            host: 主机
            privilege: 权限类型
            database: 数据库名（可选）
            table: 表名（可选）
            
        Returns:
            bool: 授权成功返回True
        """
        if not self.auth_manager:
            self.logger.error("USER_MGMT", 0, "Auth manager not initialized")
            return False
        
        try:
            # 检查当前用户是否有权限授予权限
            session = self.get_session(session_id)
            if not session or not session.is_authenticated:
                self.logger.error("USER_MGMT", 0, "User not authenticated")
                return False
            # 权限检查：需要GRANT_OPTION
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "grant_privilege", database, table)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to grant privilege: {perm.reason}")
                    return False
            
            # 确定资源类型和名称
            if table and database:
                # 表级权限
                resource_type = "TABLE"
                resource_name = f"{database}.{table}"
            elif database:
                # 数据库级权限
                resource_type = "DATABASE"
                resource_name = database
            else:
                # 全局权限
                resource_type = "GLOBAL"
                resource_name = "*"
            
            # 使用认证管理器授予权限，注意使用auth_session_id而不是db_session_id
            auth_session_id = session.auth_session_id
            success = self.auth_manager.grant_privilege(
                auth_session_id, privilege, resource_type, resource_name, username, host
            )
            if success:
                self.logger.info("USER_MGMT", 0, 
                               f"Granted {privilege} to {username}@{host} on {database or '*'}.{table or '*'}")
            return success
            
        except Exception as e:
            self.logger.error("USER_MGMT", 0, f"Failed to grant privilege: {e}")
            return False
    
    def revoke_privilege(self, session_id: str, username: str, host: str,
                        privilege: str, database: str = None, table: str = None) -> bool:
        """
        撤销权限
        
        Args:
            session_id: 会话ID
            username: 用户名
            host: 主机
            privilege: 权限类型
            database: 数据库名（可选）
            table: 表名（可选）
            
        Returns:
            bool: 撤销成功返回True
        """
        if not self.auth_manager:
            self.logger.error("USER_MGMT", 0, "Auth manager not initialized")
            return False
        
        try:
            # 检查当前用户是否有权限撤销权限
            session = self.get_session(session_id)
            if not session or not session.is_authenticated:
                self.logger.error("USER_MGMT", 0, "User not authenticated")
                return False
            # 权限检查：需要GRANT_OPTION
            if self.auth_manager:
                perm = self.auth_manager.check_permission(session.auth_session_id, "revoke_privilege", database, table)
                if not perm.allowed:
                    self.logger.warn("AUTH", 0, f"Permission denied to revoke privilege: {perm.reason}")
                    return False
            
            # 确定资源类型和名称
            if table and database:
                # 表级权限
                resource_type = "TABLE"
                resource_name = f"{database}.{table}"
            elif database:
                # 数据库级权限
                resource_type = "DATABASE"
                resource_name = database
            else:
                # 全局权限
                resource_type = "GLOBAL"
                resource_name = "*"
            
            # 使用认证管理器撤销权限，注意使用auth_session_id而不是db_session_id
            auth_session_id = session.auth_session_id
            success = self.auth_manager.revoke_privilege(
                auth_session_id, privilege, resource_type, resource_name, username, host
            )
            if success:
                self.logger.info("USER_MGMT", 0, 
                               f"Revoked {privilege} from {username}@{host} on {database or '*'}.{table or '*'}")
            return success
            
        except Exception as e:
            self.logger.error("USER_MGMT", 0, f"Failed to revoke privilege: {e}")
            return False
    
    def get_user_privileges(self, session_id: str, username: str = None, host: str = None) -> List[Dict[str, Any]]:
        """
        获取用户权限信息
        
        Args:
            session_id: 会话ID
            username: 用户名（可选，默认获取当前用户）
            host: 主机（可选）
            
        Returns:
            List[Dict[str, Any]]: 权限列表
        """
        if not self.auth_manager:
            self.logger.error("USER_MGMT", 0, "Auth manager not initialized")
            return []
        
        try:
            session = self.get_session(session_id)
            if not session or not session.is_authenticated:
                self.logger.error("USER_MGMT", 0, "User not authenticated")
                return []
            
            # 如果没有指定用户，获取当前用户的权限
            if username is None:
                username = session.username
                host = session.user_host
            else:
                # 查询他人权限需要提升权限
                if self.auth_manager and (username != session.username or (host and host != session.user_host)):
                    perm = self.auth_manager.check_permission(session.auth_session_id, "get_user_privileges", None, None)
                    if not perm.allowed:
                        self.logger.warn("AUTH", 0, f"Permission denied to get other user's privileges: {perm.reason}")
                        return []
            
            # 使用权限管理器获取用户权限
            privileges = self.auth_manager.permission_manager.get_user_privileges(username, host)
            if isinstance(privileges, list):
                # 将 PrivilegeInfo 对象转换为字典格式
                privilege_dicts = []
                for privilege in privileges:
                    if hasattr(privilege, '__dict__'):
                        privilege_dict = {
                            'privilege_type': privilege.privilege_type.value if hasattr(privilege.privilege_type, 'value') else str(privilege.privilege_type),
                            'resource_type': privilege.resource_type.value if hasattr(privilege.resource_type, 'value') else str(privilege.resource_type),
                            'resource_name': privilege.resource_name,
                            'is_grantable': privilege.is_grantable,
                            'granted_time': privilege.granted_time.isoformat() if hasattr(privilege.granted_time, 'isoformat') else str(privilege.granted_time),
                            'granted_by': privilege.granted_by
                        }
                        privilege_dicts.append(privilege_dict)
                return privilege_dicts
            else:
                return []
            
        except Exception as e:
            self.logger.error("USER_MGMT", 0, f"Failed to get user privileges: {e}")
            return []


def create_default_server(db_path: str = "test_db") -> DatabaseServer:
    """创建默认配置的数据库服务器"""
    config = DatabaseConfig(db_path=db_path)
    return DatabaseServer(config)


if __name__ == "__main__":
    # 简单的测试代码
    print("创建数据库服务器...")
    server = create_default_server("demo_db")
    
    try:
        server.start()
        print("服务器启动成功")
        
        # 创建会话
        session_id = server.create_session()
        print(f"创建会话: {session_id}")
        
        # 简单测试
        print("开始测试...")
        
        # 创建表
        columns = [
            Column("id", ColumnType.INTEGER, primary_key=True),
            Column("name", ColumnType.VARCHAR, length=50),
            Column("age", ColumnType.INTEGER)
        ]
        result = server.execute_query(session_id, "create_table", table_name="test_users", columns=columns)
        print(f"创建表结果: {result}")
        
        # 获取表列表
        tables = server.execute_query(session_id, "get_table_list")
        print(f"表列表: {tables}")
        
        # 获取服务器统计
        stats = server.get_server_stats()
        print(f"服务器统计: {stats}")
        
        print("测试完成")
        
    except Exception as e:
        print(f"测试失败: {e}")
        import traceback
        traceback.print_exc()
    
    finally:
        server.stop()
        print("服务器已停止")
